﻿
//Copyright (c) Microsoft
//All rights reserved
//Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0 
//THIS CODE IS PROVIDED *AS IS* BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OR CONDITIONS OF TITLE, FITNESS FOR A PARTICULAR PURPOSE, MERCHANTABLITY OR NON-INFRINGEMENT. 
//See the Apache Version 2.0 License for specific language governing permissions and limitations under the License.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Runtime;
using System.Text.RegularExpressions;
using System.IO;
using Microsoft.DataTransformationServices.Controls;

namespace PackageConfigs
{
    public partial class ConfigEditor : Form
    {       
        //Constructor
        public ConfigEditor()
        {                        
            InitializeComponent();            
        }

        #region Event Handlers

        private void btnLoadPackages_Click(object sender, EventArgs e)
        {                 
            try
            {
                SetBusy(true, "Loading Packages...");                
                //Get the List of Packages under the specified SSIS Folder
                if (rdoSql.Checked)
                {
                    List<PackageRecord> packageRecords = GetPackages(txtPackageFolder.Text.Trim(), txtServerName.Text.Trim(), txtUsername.Text.Trim(), txtPassword.Text);
                    //Populate the Grid with the packages
                    dgvPackages.DataSource = packageRecords;
                }
                //Get the List of Packages under the specified File System Folder
                else if (rdoFile.Checked)
                {
                    List<PackageRecord> packageRecords = GetPackages(txtFileSystemFolder.Text.Trim());
                    //Populate the Grid with the packages
                    dgvPackages.DataSource = packageRecords;
                }
                else
                {
                    List<PackageRecord> packageRecords = GetPackages(txtPackageFolder.Text.ToString(), txtServerName.Text.ToString());
                    //Populate the Grid with the packages
                    dgvPackages.DataSource = packageRecords;
                }
                //Scan each package's configuration and determine whether the config file paths are valid
                foreach (DataGridViewRow row in dgvPackages.Rows)
                {                    
                    if ((bool)row.Cells["AreAllConfigPathsCorrect"].Value)
                    {
                        //Set Color if Config Files are Valid
                        row.DefaultCellStyle.BackColor = Color.White;
                        row.DefaultCellStyle.ForeColor = Color.Black;
                        row.Cells["ConfigPaths"].Style.ForeColor = Color.Black;
                    }
                    else
                    {
                        //Set Color if Config Files are Invalid                        
                        row.Cells["ConfigPaths"].Style.ForeColor = Color.Red;
                        row.DefaultCellStyle.ForeColor = Color.Black;
                        row.Cells["Path"].Style.ForeColor = Color.Red;
                        //Set ToolTip Text to enhance usability
                        row.Cells["ConfigPaths"].ToolTipText = "Double Click to display the Invalid Configuration File path";                        
                    }
                    //Mark the cells Read-only for packages which do not have a config file association
                    if (row.Cells["ConfigPaths"].Value.ToString().Trim() == "")
                    {
                        row.Cells["ConfigPaths"].Value = "No Configuration File associated with this package";                        
                        row.Cells["ConfigPaths"].Style.Font = new System.Drawing.Font("Century", 9.75F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
                        row.Cells["ConfigPaths"].Style.Font = new System.Drawing.Font("Century", 9.75F, System.Drawing.FontStyle.Italic, System.Drawing.GraphicsUnit.Point, ((byte)(0)));                        
                        row.Cells["ConfigPaths"].ReadOnly = true;
                    }
                }
                SetBusy(false, "");
               
            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("Error: {0}", ex.Message),"Package Load Error");
                SetBusy(false, "");
            }
        }

        private void btnApply_Click(object sender, EventArgs e)
        {
            //Push the changes back to the Package
            SetBusy(true, "Applying Changes..");
            if (rdoSql.Checked)
            {
                SavePackagesToSqlServer();
            }
            else if (rdoFile.Checked)
            {
                SavePackagesToFileSystem();
            }
            else
            {
                SavePackagesToPackageStore();
            }
            SetBusy(false, "");                  
        }             

        private void btnSelectSqlFolder_Click(object sender, EventArgs e)
        {
            ServerPackagesForm sqlFolder = new ServerPackagesForm();
            if (rdoSql.Checked)
            {
                if (chkWinAuth.Checked)
                {
                    sqlFolder = ServerPackagesForm.CreateSqlServerEnumerationForm(txtServerName.Text.Trim(), null, null, true);
                }
                else
                {
                    sqlFolder = ServerPackagesForm.CreateSqlServerEnumerationForm(txtServerName.Text.Trim(), txtUsername.Text.Trim(), txtPassword.Text.Trim(), true);
                }
            }
            else if (rdoPackageStore.Checked)
            {
                sqlFolder = ServerPackagesForm.CreateDtsServerEnumerationForm(txtServerName.Text.Trim(), true);
            }            
            sqlFolder.ShowDialog();
            txtPackageFolder.Text = sqlFolder.SelectedPath;
        }

        private void btnSelectPath_Click(object sender, EventArgs e)
        {
            folderSelectPath.ShowDialog();
            txtFileSystemFolder.Text = folderSelectPath.SelectedPath;
        }

        private void rdoSql_CheckedChanged(object sender, EventArgs e)
        {
            pnlSql.Enabled = rdoSql.Checked;
            pnlFileSystem.Enabled = !rdoSql.Checked;
            txtPackageFolder.Text = @"Data Collector";
            InitGrid();
            //+++++++++++++++++++++++TO DO++++++++++++++++++++++++++
            //This is done coz Parsing sub folders from File System is 
            //currently not implemented and we disable this check box
            //on seclection of File System loading
            //Need to remove below code once that is done
            chkSubfolders.Enabled = true;
            //+++++++++++++++++++++++TO DO++++++++++++++++++++++++++            
        }

        private void rdoFile_CheckedChanged(object sender, EventArgs e)
        {
            pnlFileSystem.Enabled = rdoFile.Checked;

            //+++++++++++++++++++++++TO DO++++++++++++++++++++++++++
            //Parsing sub folders from File System is currently not implemented 
            //Need to remove below code once that is done
            chkSubfolders.Checked = false;
            chkSubfolders.Enabled = false;
            //+++++++++++++++++++++++TO DO++++++++++++++++++++++++++
        }

        private void rdoPackageStore_CheckedChanged(object sender, EventArgs e)
        {
            pnlFileSystem.Enabled = !rdoPackageStore.Checked;
            pnlSql.Enabled = rdoPackageStore.Checked;
            txtUsername.Enabled = !rdoPackageStore.Checked;
            txtPassword.Enabled = !rdoPackageStore.Checked;
            chkWinAuth.Enabled = !rdoPackageStore.Checked;
            chkWinAuth.Checked = rdoPackageStore.Checked;
            txtPackageFolder.Text = @"MSDB";
        }

        private void ConfigEditor_Load(object sender, EventArgs e)
        {
            InitControls();
        }

        private void chkWinAuth_CheckedChanged(object sender, EventArgs e)
        {
            if (chkWinAuth.Checked)
            {
                txtUsername.Text = "";
                txtPassword.Text = "";
            }
            txtUsername.Enabled = !chkWinAuth.Checked;
            txtPassword.Enabled = !chkWinAuth.Checked;
        }

        #endregion                                          //End of Event Handlers

        #region Private methods

        private void InitControls()
        {
            chkWinAuth.Checked = true;
            rdoSql.Checked = true;
            pnlSql.Enabled = (rdoSql.Checked || rdoPackageStore.Checked);
            pnlFileSystem.Enabled = rdoFile.Checked;
        }

        //Get Packages from Sql Server
        private List<PackageRecord> GetPackages(string packagesFolder, string serverName, string userName, string password)
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            PackageInfos packageInfos;
            if (chkWinAuth.Checked)
            {
                packageInfos = app.GetPackageInfos(packagesFolder, serverName, null, null);
            }
            else
            {
                packageInfos = app.GetPackageInfos(packagesFolder, serverName, userName, password);
            }
            //Populate the PackageRecords class to get details like CreationDate, Size etc.
            List<PackageRecord> packageRecords = new List<PackageRecord>(packageInfos.Count);
            foreach (var packageInfo in packageInfos)
            {
                //Format the Path
                string packagePath = (packageInfo.Folder != "\\" ? packageInfo.Folder : "") + "/" + packageInfo.Name;
                if (packageInfo.Flags == DTSPackageInfoFlags.Folder) //Scan through sub-folder for packages within it
                {
                    if (chkSubfolders.Checked)
                        packageRecords.AddRange(GetPackages(packagePath, serverName, userName, password));
                }
                else
                {
                    //Loaded object is a package. Load the package to get the configuration details
                    Package pkg=new Package();
                    if (!chkWinAuth.Checked) //SQl Auth
                    {
                        pkg = app.LoadFromSqlServer(packagePath, serverName, userName, password, null);
                    }
                    else                     //Win Auth
                    {
                        pkg = app.LoadFromSqlServer(packagePath, serverName, null, null, null);
                    }
                    string configPaths = "";
                    bool valid = true;                              //Assume all config files to be valid
                    foreach (var cfg in pkg.Configurations)
                    {
                        //We are concerned only with Configuration Files, not with other Configurations like Parent variable etc.
                        if (cfg.ConfigurationType.ToString() == "ConfigFile")
                        {
                            configPaths += cfg.ConfigurationString.Trim() + ";";      //Load Config File(s) separated by semi-colon
                            string[] configFiles = configPaths.Split(';');            //Load Each Config File entry in array index
                            for (int i = 0; i < configFiles.Count() - 1; i++)         //We have a semicolon in the beginning, so the count is till -1
                            {
                                if (File.Exists(configFiles[i]))                      //Check if the Config File path exists and is valid
                                {
                                    //valid = true;               //Don't need to set it any more, we are already considering
                                                                  //all config paths to be valid
                                }
                                else
                                {
                                    valid = false;                 //Config file path is invalid
                                }
                            }
                        }
                        else                                                     
                        {
                            //valid = true;           //Don't need to set it any more, we are already considering
                                                      //all config paths to be valid                              
                        }
                    }
                    //Return the Package Record with details
                    PackageRecord pr = new PackageRecord { Path = packagePath, ConfigPaths = configPaths, AreAllConfigPathsCorrect = valid, CreationDate = packageInfo.CreationDate };
                    packageRecords.Add(pr);
                }
            }
            return packageRecords;
        }

        //Get Packages from File System
        private List<PackageRecord> GetPackages(string packagesFolder)
        {
            {
                //This variable will keep track of all errors while loading each package
                StringBuilder errors = new StringBuilder();
                //Gets all packages from the specified folder
                var packagePaths = System.IO.Directory.GetFiles(packagesFolder, "*.dtsx");

                List<PackageRecord> packageRecords = new List<PackageRecord>(packagePaths.Length);

                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                
                foreach (var packagePath in packagePaths)
                {
                    try
                    {
                        bool valid = true; //We will consider all config paths to be valid unless we explicitly find 1 invalid
                        Package pkg = app.LoadPackage(packagePath, null);
                        string configPaths = "";
                        foreach (var cfg in pkg.Configurations)
                        {
                            //We are concerned only with Configuration Files, not with other Configurations like Parent variable etc.
                            if (cfg.ConfigurationType.ToString() == "ConfigFile")
                            {
                                configPaths += cfg.ConfigurationString.Trim() + ";";      //Load Config File(s) separated by semi-colon
                                string[] configFiles = configPaths.Split(';');            //Load Each Config File entry in array index
                                for (int i = 0; i < configFiles.Count() - 1; i++)         //We have a semicolon in the beginning, so the count is till -1
                                {
                                    if (File.Exists(configFiles[i]))                      //Check if the Config File path exists and is valid
                                    {
                                        //valid = true; //Don't need to set it any more, we are already considering
                                        //all config paths to be valid
                                    }
                                    else
                                    {
                                        valid = false;  //Set this flag false only if we detect an invalid config path
                                    }
                                }
                            }
                            else                                                          //Package Does not have Config Files, hence considered valid
                            {
                                //valid = true;  //Don't need to set it any more, we are already considering
                                //all config paths to be valid
                            }
                        }

                        PackageRecord pr = new PackageRecord { Path = packagePath, CreationDate = pkg.CreationDate, AreAllConfigPathsCorrect = valid, ConfigPaths = configPaths };
                        packageRecords.Add(pr);
                    }
                    catch (Exception ex)
                    {
                        errors.AppendFormat("{0} - Error: {1}" + Environment.NewLine, packagePath, ex.Message);
                    }
                }
                if (errors.ToString() != "")
                    MessageBox.Show("Unable to load below package(s): " + Environment.NewLine + Environment.NewLine + errors,"Package Load Error");

                return packageRecords;
            }
        }

        //Get Packages from SSIS Package Store
        private List<PackageRecord> GetPackages(string packageStore, string serverName)
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            PackageInfos packageInfos = app.GetDtsServerPackageInfos(packageStore, serverName);

            List<PackageRecord> packageRecords = new List<PackageRecord>(packageInfos.Count);

            foreach (var packageInfo in packageInfos)
            {
                //Format the Path
                string packagePath = (packageInfo.Folder != "\\" ? packageInfo.Folder : "") + "/" + packageInfo.Name;
                if (packageInfo.Flags == DTSPackageInfoFlags.Folder) //Scan through sub-folder for packages within it
                {
                    if (chkSubfolders.Checked)
                        packageRecords.AddRange(GetPackages(packagePath, serverName, null,null));
                }
                else
                {
                    //Loaded object is a package. Load the package to get the configuration details
                    Package pkg = app.LoadFromDtsServer(packagePath, serverName, null);
                    string configPaths = "";
                    bool valid = true;                              //Assume all config files to be valid
                    foreach (var cfg in pkg.Configurations)
                    {
                        //We are concerned only with Configuration Files, not with other Configurations like Parent variable etc.
                        if (cfg.ConfigurationType.ToString() == "ConfigFile")
                        {
                            configPaths += cfg.ConfigurationString.Trim() + ";";      //Load Config File(s) separated by semi-colon
                            string[] configFiles = configPaths.Split(';');            //Load Each Config File entry in array index
                            for (int i = 0; i < configFiles.Count() - 1; i++)         //We have a semicolon in the beginning, so the count is till -1
                            {
                                if (File.Exists(configFiles[i]))                      //Check if the Config File path exists and is valid
                                {
                                    //valid = true;               //Don't need to set it any more, we are already considering
                                    //all config paths to be valid
                                }
                                else
                                {
                                    valid = false;                 //Config file path is invalid
                                }
                            }
                        }
                        else
                        {
                            //valid = true;           //Don't need to set it any more, we are already considering
                            //all config paths to be valid                              
                        }
                    }
                    //Return the Package Record with details
                    PackageRecord pr = new PackageRecord { Path = packagePath, ConfigPaths = configPaths, AreAllConfigPathsCorrect = valid, CreationDate = packageInfo.CreationDate };
                    packageRecords.Add(pr);
                }
            }
            return packageRecords;
        }

        //Save Packages to Sql Server
        private void SavePackagesToSqlServer()
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            //Scan each package loaded
            foreach (DataGridViewRow row in dgvPackages.Rows)
            {
                //Load the Package
                Package pkg = app.LoadFromSqlServer(row.Cells["Path"].Value.ToString(), txtServerName.Text.Trim(), txtUsername.Text.Trim(), txtPassword.Text, null);
                //Get the Config File paths
                string[] configPaths = row.Cells["ConfigPaths"].Value.ToString().Trim().Split(';');
                string configPath = "";
                for (int i = 0; i < configPaths.Count() - 1; i++)
                {
                    //Remove all existing Configurations before adding the New ones
                    for (int x = 0; x < pkg.Configurations.Count; x++)
                    {
                        pkg.Configurations.Remove(x);
                    }
                    //Now add the new entries and save the Package
                    Configuration config = pkg.Configurations.Add();
                    config.ConfigurationString = configPath.Trim() + configPaths[i].Trim();
                    config.ConfigurationType = DTSConfigurationType.ConfigFile;                    
                    app.SaveToSqlServer(pkg, null, txtServerName.Text.Trim(), txtUsername.Text.Trim(), txtPassword.Text.Trim());
                }
            }
            //Load Packages to re-populate Grid
            if (dgvPackages.Rows.Count!=0) btnLoadPackages_Click(null, null);
        }

        //Save Packages to File System
        private void SavePackagesToFileSystem()
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            //Scan each package loaded
            foreach (DataGridViewRow row in dgvPackages.Rows)
            {
                //Load the Package
                Package pkg = app.LoadPackage(row.Cells["Path"].Value.ToString(), null);
                //Get the Config File paths
                if (row.Cells["ConfigPaths"].Value != null)
                {

                    //Remove all existing Configurations before adding the New ones
                    int pcnt = pkg.Configurations.Count;
                    for (int x = pcnt; x > 0; x--)
                    {
                        pkg.Configurations.Remove(x - 1);
                    }

                    //Cleaning the configuration path string
                    row.Cells["ConfigPaths"].Value = row.Cells["ConfigPaths"].Value.ToString().Trim();

                    if (row.Cells["ConfigPaths"].Value.ToString().EndsWith(";"))
                    {
                        row.Cells["ConfigPaths"].Value = row.Cells["ConfigPaths"].Value.ToString().Substring(0, row.Cells["ConfigPaths"].Value.ToString().Length - 1);
                    }

                    if (row.Cells["ConfigPaths"].Value.ToString().StartsWith(";"))
                    {
                        row.Cells["ConfigPaths"].Value = row.Cells["ConfigPaths"].Value.ToString().Substring(1, row.Cells["ConfigPaths"].Value.ToString().Length - 1);
                    }

                    if (row.Cells["ConfigPaths"].Value.ToString().Contains(";"))
                    {                        
                        row.Cells["ConfigPaths"].Value = row.Cells["ConfigPaths"].Value.ToString().Trim();
                        row.Cells["ConfigPaths"].Value = row.Cells["ConfigPaths"].Value.ToString().Replace(";;", ";");

                        string[] configPaths = row.Cells["ConfigPaths"].Value.ToString().Split(';');

                        int ccnt = configPaths.Count();
                        for (int i = ccnt; i > 0; i--)
                        {
                            if (configPaths[i - 1].Trim() != ";" & configPaths[i - 1].Trim() != "")
                            {
                                //Now add the new entries and save the Package
                                Configuration config = pkg.Configurations.Add();
                                
                                config.ConfigurationString = configPaths[i - 1].Trim();
                                config.ConfigurationString = config.ConfigurationString.Replace(";", "");
                                config.ConfigurationType = DTSConfigurationType.ConfigFile;                                
                                app.SaveToXml(row.Cells["Path"].Value.ToString(), pkg, null);
                            }
                        }
                    }
                    else
                    {
                        //Now add the new entries and save the Package
                        Configuration config = pkg.Configurations.Add();
                        config.ConfigurationString = row.Cells["ConfigPaths"].Value.ToString();
                        config.ConfigurationType = DTSConfigurationType.ConfigFile;                        
                        app.SaveToXml(row.Cells["Path"].Value.ToString(), pkg, null);

                    }
                }
            }
            //Load Packages to re-populate Grid
            if (dgvPackages.Rows.Count != 0) btnLoadPackages_Click(null, null);
        }

        //Save Packages to SSIS Package Store
        private void SavePackagesToPackageStore()
        {
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            //Scan each package loaded
            foreach (DataGridViewRow row in dgvPackages.Rows)
            {
                //Load the Package
                Package pkg = app.LoadFromDtsServer(row.Cells["Path"].Value.ToString(), txtServerName.Text.Trim(), null);
                //Get the Config File paths
                string[] configPaths = row.Cells["ConfigPaths"].Value.ToString().Trim().Split(';');
                string configPath = "";
                for (int i = 0; i < configPaths.Count() - 1; i++)
                {
                    //Remove all existing Configurations before adding the New ones
                    for (int x = 0; x < pkg.Configurations.Count; x++)
                    {
                        pkg.Configurations.Remove(x);
                    }
                    //Now add the new entries and save the Package
                    Configuration config = pkg.Configurations.Add();
                    config.ConfigurationString = configPath.Trim() + configPaths[i].Trim();
                    config.ConfigurationType = DTSConfigurationType.ConfigFile;                    
                    app.SaveToDtsServer(pkg, null,row.Cells["Path"].Value.ToString(), txtServerName.Text.Trim());
                }
            }
            //Load Packages to re-populate Grid
            if (dgvPackages.Rows.Count != 0) btnLoadPackages_Click(null, null);
        }

        private void SetBusy(bool busy, string message)
        {
            if (busy)
            {
                lblBusyMessage.Text = message;
                pnlBusy.Visible = true;
                pnlMain.Enabled = false;
                UseWaitCursor = true;
            }
            else
            {
                pnlBusy.Visible = false;
                pnlMain.Enabled = true;
                UseWaitCursor = false;
            }
        }

        private void InitGrid()
        {
            DataTable dummy = new DataTable("DummyTable");

            dummy.Columns.Add("Path");
            dummy.Columns.Add("CreationDate");
            dummy.Columns.Add("AreAllConfigPathsCorrect");
            dummy.Columns.Add("ConfigPaths");
            dgvPackages.DataSource = dummy.DefaultView;
        }

        #endregion                                              //End of private methods
    }
 }

